package com.jf.cloud.service.sysmanager.dao.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlRowSetResultSetExtractor;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jf.cloud.service.sysmanager.common.base.BaseService;
import com.jf.cloud.service.sysmanager.common.base.OPaging;
import com.jf.cloud.service.sysmanager.common.db.BaseDao;
import com.jf.cloud.service.sysmanager.common.db.jdbc.RowMapperFactory;
import com.jf.cloud.service.sysmanager.common.taglib.Paging;
import com.jf.cloud.service.sysmanager.common.util.DateUtil;
import com.jf.cloud.service.sysmanager.common.util.StringUtil;
import com.jf.cloud.service.sysmanager.dao.ICardUserService;
import com.jf.cloud.service.sysmanager.entity.CardBuguUserInfo;
import com.jf.cloud.service.sysmanager.entity.CardUser;
import com.jf.cloud.service.sysmanager.entity.SysStaff;

/**
 * Created with IntelliJ IDEA.
 * User: LangK
 * Created Date 2018/5/28
 * Time: 14:25
 * Description:
 */
@Service
public class CardUserServiceImpl extends BaseService<CardUser, Long> implements ICardUserService {

    /**
     * 日志
     */
    private static Log log = LogFactory.getLog(CardUserServiceImpl.class);

    /**
     * JDBC 模板
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * JDBC 模板
     */
    @Autowired
    private BaseDao baseDao;

    @Override
    public CardUser updateNotNullColumn(CardUser cardUser) {
        if (cardUser.getRowId() == 0) {
            return null;
        }
        List<Object> list = new ArrayList<Object>();
        StringBuilder sql = new StringBuilder("update card_base_user set ");
        if (StringUtil.isNotEmpty(cardUser.getUserName())) {
            sql.append("USER_NAME = ? ,");
            list.add(cardUser.getUserName());
        }
        if (StringUtil.isNotEmpty(cardUser.getEmailPwd())) {
            sql.append("EMAIL_PWD = ? ,");
            list.add(cardUser.getEmailPwd());
        }
        if (cardUser.getFailId() != null) {
            sql.append("FAIL_ID = ? ,");
            list.add(cardUser.getFailId());
        }
        //if (StringUtil.isNotEmpty(cardUser.getFailReason())) {
        sql.append("FAIL_REASON = ? ,");
        list.add(cardUser.getFailReason());
        //}
        if (cardUser.getFailTime() != null) {
            sql.append("FAIL_TIME = ? ,");
            list.add(cardUser.getFailTime());
        }
        if (StringUtil.isNotEmpty(cardUser.getLoginPwd())) {
            sql.append("LOGIN_PWD = ? ,");
            list.add(cardUser.getLoginPwd());
        }
        if (StringUtil.isNotEmpty(cardUser.getPayPwd())) {
            sql.append("PAY_PWD = ? ,");
            list.add(cardUser.getPayPwd());
        }
        if (cardUser.getUserEnable() != null) {
            sql.append("USER_ENABLE = ? ,");
            list.add(cardUser.getUserEnable());
        }
        if (cardUser.getUserStatus() != null) {
            sql.append("USER_STATUS = ? ,");
            list.add(cardUser.getUserStatus());
        }
        if (StringUtil.isNotEmpty(cardUser.getUserKey())) {
            sql.append("USER_KEY = ? ,");
            list.add(cardUser.getUserKey());
        }
        if (sql.lastIndexOf(",") == sql.length() - 1) {
            sql.deleteCharAt(sql.length() - 1);
        }
        sql.append("WHERE ROW_ID=?");
        list.add(cardUser.getRowId());
        int count = update(sql.toString(), list.toArray());
        if (count > 0) {
            return cardUser;
        }
        return null;
    }

//    @Transactional
//    @Override
//    public boolean allocation(Integer platformType, String projectName, Map<Long, Integer> data, SysStaff sysStaff) {
//    	try {
//    		if (data == null || data.isEmpty()) {
//    			return false;
//    		}
//
//    		// 保存批次
//    		CardBatch cardBatch = new CardBatch();
//    		cardBatch.setPlatformType(platformType);
//    		cardBatch.setProjectName(projectName);
//    		cardBatch = baseDao.saveAll(cardBatch);
//
//    		// 更新帐号分配状态SQL
//    		String updateTempSql = "UPDATE card_base_user SET USER_STATUS = 1, KF_ID = ?, KF_ACCOUNT = ?, PROJECT_NAME = ?, ";
//    		updateTempSql += " OPT_ID = ?, OPT_ACCOUNT = ?, LAST_OPT_TIME = ? ";
//    		updateTempSql += " WHERE USER_STATUS = 0 AND ROW_ID IN ";
//    		String updateSql = "";
//    		String rowIds = "";
//
//    		// 查询该客服分配帐号数量SQL
//    		List<CardUserVO> list = null;
//    		String tempSql = "SELECT ROW_ID, USER_NAME FROM card_base_user ";
//    		tempSql += " WHERE USER_STATUS = 0 and USER_ENABLE = 0 AND PLATFORM_TYPE = ? ";
//    		String selectSql = "";
//
//    		// 帐号操作记录（生成对象、保存对象时使用）
//    		CardOptRecord cardOptRecord = null;
//    		List<CardOptRecord> corList = new ArrayList<CardOptRecord>();
//
//    		// 客服信息
//    		String kfAccount = null;
//    		String kfAccountSql = "SELECT STAFF_ACCOUNT FROM sys_staff WHERE ROW_ID = ? ";
//
//    		// 遍历客服信息
//    		Iterator<Entry<Long, Integer>> it = data.entrySet().iterator();
//    		Entry<Long, Integer> entry = null;
//    		while (it.hasNext()) {
//    			entry = it.next();
//
//    			selectSql = tempSql + " LIMIT " + entry.getValue();
//    			list = baseDao.findList(selectSql, RowMapperFactory.newInstance(CardUserVO.class), platformType);
//
//    			if (list == null || list.size() < Integer.valueOf(entry.getValue() + "")) {
//    				throw new RuntimeException("客服【" + entry.getKey() + "】，要分配 " + entry.getValue() + "个帐号，实际只查询到" + list.size());
//    			}
//
//    			// 构建客服操作记录
//    			for (CardUserVO item : list) {
//    				cardOptRecord = new CardOptRecord();
//    				cardOptRecord.setStaffId(Long.valueOf(entry.getKey() + ""));
//    				cardOptRecord.setBatchId(cardBatch.getRowId());
//    				cardOptRecord.setUserId(item.getRowId());
//    				cardOptRecord.setUserName(item.getUserName());
//    				cardOptRecord.setOptResult(0);
//    				cardOptRecord.setCreateTime(DateUtil.getCurrentDateTime());
//    				cardOptRecord.setTicketNum(0);
//    				cardOptRecord.setRechargeNum(0.0);
//    				corList.add(cardOptRecord);
//    				rowIds += item.getRowId() + ",";
//    			}
//
//    			// 保存对象
//    			for (CardOptRecord cardOptRecord2 : corList) {
//    				this.baseDao.saveAll(cardOptRecord2);
//				}
//
//    			kfAccount = this.baseDao.find(kfAccountSql, String.class, Long.valueOf(entry.getKey() + ""));
//
//    			// 更新帐号分配状态
//    			rowIds = rowIds.substring(0, rowIds.length() - 1);
//    			updateSql = updateTempSql + "(" + rowIds + ")";
//    			this.baseDao.safeUpdate(updateSql, list.size(),
//    					corList.get(0).getStaffId(), kfAccount, cardBatch.getProjectName(),
//    					sysStaff.getRowId(), sysStaff.getStaffAccount(), DateUtil.getCurrentDateTime());
//    			corList.clear();
//    			rowIds = "";
//    		}
//    		return true;
//    	} catch (Exception e) {
//    		throw new RuntimeException("分配失败", e);
//    	}
//    }

    @Transactional
    @Override
    public boolean allocation(Integer platformType, String projectName, Map<Long, Integer> data, SysStaff sysStaff) {
        try {
            if (data == null) {
                return false;
            }
            String date = DateUtil.getCurrentDateTime();

            String sql = "select max(ROW_ID) from card_base_batch";

            Long batchId = baseDao.find(sql, Long.class);
            if (batchId!=null&&batchId > 0) {
                batchId++;
            } else {
                batchId = 1l;
            }

            //更新记录表里面所有未完成的记录
            sql = "update card_base_opt_record set OPT_RESULT = 3 WHERE OPT_RESULT=0 AND BATCH_ID in (select ROW_ID FROM card_base_batch WHERE PLATFORM_TYPE = ?)";
            baseDao.safeUpdate(sql, 0, platformType);

            //插入批次表
            sql = "insert into card_base_batch (ROW_ID,PROJECT_NAME, PLATFORM_TYPE,OPT_ID,OPT_TIME) values (?,?, ?,?,?)";
            baseDao.safeUpdate(sql, 0, batchId, projectName, platformType,sysStaff.getRowId(),date);

            //查询用户表可使用的用户列表
            sql = "select ROW_ID, USER_NAME FROM card_base_user WHERE USER_ENABLE = 0 AND PLATFORM_TYPE =" + platformType;
            List<CardUser> list = baseDao.findList(sql, RowMapperFactory.newInstance(CardUser.class));
            if (list != null && list.size() > 0) {
                int start = 0;
                StringBuilder stringBuilder = new StringBuilder("insert into card_base_opt_record(STAFF_ID,BATCH_ID,USER_ID,USER_NAME," +
                        "OPT_RESULT,CREATE_TIME) values");

                Iterator<Entry<Long, Integer>> it = data.entrySet().iterator();
                Entry<Long, Integer> entry = null;
                while (it.hasNext()) {
                    entry = it.next();
                    List<CardUser> itemList = list.subList(start, start + Integer.valueOf(entry.getValue() + ""));
                    List<Long> rowList = new ArrayList<>();
                    if (itemList != null && itemList.size() > 0) {
                        for (CardUser item : itemList) {
                            rowList.add(item.getRowId());
                            stringBuilder.append(String.format("(%s,%s,%s,'%s',%s,'%s'),", entry.getKey(), batchId, item.getRowId(), item.getUserName(), 0, date));
                        }
                    }
                    String upSql = "select ROW_ID,STAFF_ACCOUNT FROM sys_staff WHERE ROW_ID = ?";
                    SysStaff sysStaff1 = baseDao.findObject(upSql, RowMapperFactory.newInstance(SysStaff.class), entry.getKey());
                    String rowIds = StringUtils.join(rowList.toArray(), ",");
                    sql = "UPDATE card_base_user SET USER_STATUS=1,PROJECT_NAME=?,KF_ID=?,KF_ACCOUNT=? WHERE ROW_ID in ("+rowIds+")";
                    log.info(sql);
                    baseDao.safeUpdate(sql, 0, projectName, sysStaff1.getRowId(), sysStaff1.getStaffAccount());

                    start = start + Integer.valueOf(entry.getValue() + "");
                }
                if (stringBuilder.lastIndexOf(",") == stringBuilder.length() - 1) {
                    stringBuilder.deleteCharAt(stringBuilder.length() - 1);
                }
                sql = stringBuilder.toString();
                log.info(sql);
                jdbcTemplate.execute(sql);

                //更新用户表所有用户已分配状态

                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("分配失败");
        }
    }

    @Override
    public boolean residueAllocation(Integer platformType, Map<Long, Integer> data, SysStaff sysStaff) {
        try {
            if (data == null) {
                return false;
            }
            String sql = "select max(ROW_ID) AS ID from card_base_batch where PLATFORM_TYPE=" + platformType;
            log.info(sql);
            SqlRowSet sqlRowSet = jdbcTemplate.query(sql, new SqlRowSetResultSetExtractor());
            long batchId = 0;
            if (sqlRowSet != null && sqlRowSet.next()) {
                batchId = sqlRowSet.getLong("ID");
            } else {
                return false;
            }

            //查询未完成的用户
            sql = "select USER_ID as rowId,USER_NAME AS userName FROM card_base_opt_record WHERE OPT_RESULT = 0 and BATCH_ID = " + batchId;
            log.info(sql);
            List<CardUser> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper(CardUser.class));

            //查询新增的用户
            sql = "select ROW_ID as rowId,USER_NAME AS userName FROM card_base_user WHERE USER_ENABLE = 0 and PLATFORM_TYPE = ? AND USER_STATUS = 0";
            log.info(sql);
            List<CardUser> newList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(CardUser.class), platformType);

            //合并新增用户和未完成用户
            if (list != null || newList != null) {
                if (list == null) {
                    list = new ArrayList<>();
                }
                if (newList != null) {
                    list.addAll(newList);
                }
            }

            if (list != null && list.size() > 0) {

                //删除未操作记录
                sql = "delete FROM card_base_opt_record WHERE OPT_RESULT = 0 and BATCH_ID =" + batchId;
                log.info(sql);
                jdbcTemplate.execute(sql);

                List<Long> idList = new ArrayList<>(list.size());
                for (CardUser item : list){
                    idList.add(item.getRowId());
                }
                String ids = StringUtils.join(idList.toArray(), ",");
                sql = "UPDATE card_base_user SET USER_STATUS=0,KF_ID=NULL ,KF_ACCOUNT=NULL WHERE ROW_ID in ("+ids+")";
                log.info(sql);
                baseDao.safeUpdate(sql, 0);

                int start = 0;
                String date = DateUtil.getCurrentDateTime();
                StringBuilder stringBuilder = new StringBuilder("insert into card_base_opt_record(STAFF_ID,BATCH_ID,USER_ID,USER_NAME," +
                        "OPT_RESULT,CREATE_TIME) values");
                for (long key : data.keySet()) {

                    List<CardUser> items = list.subList(start, start + data.get(key));
                    List<Long> rowList = new ArrayList<>();
                    if (items != null && items.size() > 0) {
                        for (CardUser item : items) {
                            rowList.add(item.getRowId());
                            stringBuilder.append(String.format("(%s,%s,%s,'%s',%s,'%s'),", key, batchId, item.getRowId(), item.getUserName(), 0, date));
                        }
                    }

                    String upSql = "select ROW_ID,STAFF_ACCOUNT FROM sys_staff WHERE ROW_ID = ?";
                    SysStaff sysStaff1 = baseDao.findObject(upSql, RowMapperFactory.newInstance(SysStaff.class), key);
                    String rowIds = StringUtils.join(rowList.toArray(), ",");
                    sql = "UPDATE card_base_user SET USER_STATUS=1,KF_ID=?,KF_ACCOUNT=? WHERE ROW_ID in ("+rowIds+")";
                    log.info(sql);
                    baseDao.safeUpdate(sql, 0, sysStaff1.getRowId(), sysStaff1.getStaffAccount());

                    start = start + data.get(key);
                }
                if (stringBuilder.lastIndexOf(",") == stringBuilder.length() - 1) {
                    stringBuilder.deleteCharAt(stringBuilder.length() - 1);
                }
                sql = stringBuilder.toString();
                log.info(sql);
                jdbcTemplate.execute(sql);
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("分配失败");
        }
    }

    /**
     * 查询未分配帐号数量
     *
     * @author 朱建文 创建时间：2018年5月29日 下午5:08:52
     */
    public Integer getNotAllotNum(Long platformType) {
        Integer count = 0;
        try {
            String sql = "select max(ROW_ID) AS ID from card_base_batch where PLATFORM_TYPE=" + platformType;
            log.info(sql);
            SqlRowSet sqlRowSet = jdbcTemplate.query(sql, new SqlRowSetResultSetExtractor());
            long batchId = 0;
            if (sqlRowSet != null && sqlRowSet.next()) {
                batchId = sqlRowSet.getLong("ID");
            } else {
                return 0;
            }
            sql = "SELECT COUNT(*) FROM card_base_opt_record WHERE OPT_RESULT = 0 AND BATCH_ID = ?";
            count = this.baseDao.findCount(sql, batchId);

            //查询新增的用户
            sql = "select COUNT(*) FROM card_base_user WHERE USER_ENABLE = 0 and PLATFORM_TYPE = ? AND USER_STATUS = 0";
            log.info(sql);
            count += this.baseDao.findCount(sql, platformType);
        } catch (DataAccessException e) {
            log.error("查询未分配帐号数量出错！");
        }
        return count;

    }


    /**
     * 查询平台下所有可用帐号数量
     *
     * @author 朱建文 创建时间：2018年5月29日 下午5:08:52
     */
    public Integer getUseableNum(Long platformType) {
        Integer count = 0;
        try {
            String sql = "SELECT COUNT(0) FROM card_base_user WHERE USER_ENABLE = 0 AND PLATFORM_TYPE = ?";
            count = this.baseDao.findCount(sql, platformType);
        } catch (DataAccessException e) {
            log.error("查询未分配帐号数量出错！");
        }
        return count;

    }
    
    @Override
    public Paging<CardBuguUserInfo> findPage(OPaging<CardBuguUserInfo> page,String userName, String userEmail) {
        String sql = " SELECT * FROM card_bugu_user_info ";
        String countSql = "SELECT count(0) FROM card_bugu_user_info ";
        List list = new ArrayList();
        if (StringUtils.isNotEmpty(userName)) {
            sql = sql + " where ROW_ID IS NOT NULL AND USER_NAME like ? ";
            countSql = countSql + " where ROW_ID IS NOT NULL AND USER_NAME like ? ";
            list.add("%" + userName + "%");
            if (StringUtil.isNotEmpty(userEmail)) {
                sql = sql + " and USER_EMAIL LIKE ?";
                countSql = countSql + " and USER_EMAIL LIKE ? ";
                list.add("%" + userEmail + "%");
            }
        }else if (StringUtil.isNotEmpty(userEmail)) {
            sql += " WHERE ROW_ID IS NOT NULL AND USER_EMAIL LIKE ? ";
            countSql += " WHERE ROW_ID IS NOT NULL AND USER_EMAIL LIKE ? ";
            list.add("%" + userEmail + "%");
        } else {
        	sql += " WHERE ROW_ID IS NOT NULL ";
        	countSql += " WHERE ROW_ID IS NOT NULL ";
		}
        int count = baseDao.findCount(countSql,list.toArray());
        sql = sql + " order by CREATE_TIME DESC limit ?, ?";
        list.add(page.getOffset());
        list.add(page.getLimit());
        List<CardBuguUserInfo> resultList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CardBuguUserInfo.class),list.toArray());
        Paging<CardBuguUserInfo> paging = new Paging<CardBuguUserInfo>(count,page.getCurPage(),page.getLimit());
        paging.setDataList(resultList);
        return paging;
    }

}
